package com.android.waterdetection.dao;

import android.util.Log;

import com.android.waterdetection.bean.DeviceData;
import com.android.waterdetection.bean.UserBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBService {

    private static final String TAG = "DBService";

    /**
     * 插入用户数据（插入一条姓名为 name，密码为 pass 的数据）
     */
    public static boolean insertUser(String name, String pass) {
        // 插入数据的 sql 语句
        String sql = "insert into user (username, password) values (?, ?)";
        Connection connection = DbOpenHelper.getConnection();
        PreparedStatement ps = null;
        if (connection == null) {
            return false;
        }
        try {
            ps = connection.prepareStatement(sql);
            // 为两个 ? 设置具体的值
            ps.setString(1, name);
            ps.setString(2, pass);
            // 执行语句
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DbOpenHelper.closeConnection(connection,ps);
        }
    }

    /**
     * 删除数据（删除姓名为 name 的数据）
     */
    public static boolean deleteUser(String username) {
        // 删除数据的 sql 语句
        String sql = "delete from user where username = ?";
        Connection connection = DbOpenHelper.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            // 为 ? 设置具体的值
            ps.setString(1, username);
            // 执行语句
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DbOpenHelper.closeConnection(connection, ps);
        }
    }

    /**
     * 更新用户密码数据（将姓名为 name 的密码改为 newPassword）
     */
    public static boolean updateUserPassword(String username, String newPassword) {
        // 更新数据的 sql 语句
        String sql = "update user set password = ? where username = ?";
        Connection connection = DbOpenHelper.getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            // 为两个 ? 设置具体的值
            ps.setString(1, newPassword);
            ps.setString(2, username);
            // 执行语句
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DbOpenHelper.closeConnection(connection, ps);
        }
    }


    /**
     * 查询数据库用户全部信息（存放UserBean实体类中）
     */
    public static boolean queryUserIsCz(String username) {
        List<UserBean> list = new ArrayList<>();
        // 查询数据的 sql 语句
        String sql = "select * from user where username="+username;
        Connection connection = DbOpenHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (connection != null && (!connection.isClosed())) {
                ps = connection.prepareStatement(sql);
                if (ps != null) {
                    rs = ps.executeQuery();
                    Log.d(TAG,"rs-->" + rs);
                    if (rs != null) {
                        while (rs.next()) {
                            String name = rs.getString("username");
                            return false;
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbOpenHelper.closeConnection(connection, ps,rs);
        }
        return true;
    }


    /**
     * 查询数据库用户全部信息（存放UserBean实体类中）
     */
    public static List<UserBean> queryUser() {
        List<UserBean> list = new ArrayList<>();
        // 查询数据的 sql 语句
        String sql = "select * from user";
        Connection connection = DbOpenHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (connection != null && (!connection.isClosed())) {
                ps = connection.prepareStatement(sql);
                if (ps != null) {
                    rs = ps.executeQuery();
                    if (rs != null) {
                        while (rs.next()) {
                            UserBean userBean = new UserBean();
                            userBean.setId(rs.getInt("id"));
                            userBean.setUsername(rs.getString("username"));
                            userBean.setPassword(rs.getString("password"));
                            list.add(userBean);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbOpenHelper.closeConnection(connection, ps,rs);
        }
        return list;
    }

    /**
     * 查询数据库数据全部信息（存放DeviceData实体类中）
     */
    public static List<DeviceData> queryDeviceData() {
        List<DeviceData> list = new ArrayList<>();
        // 查询数据的 sql 语句
        String sql = "select * from data";
        Connection connection = DbOpenHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (connection != null && (!connection.isClosed())) {
                ps = connection.prepareStatement(sql);
                if (ps != null) {
                    rs = ps.executeQuery();
                    if (rs != null) {
                        while (rs.next()) {
                            DeviceData deviceData = new DeviceData();
                            deviceData.setId(rs.getInt("id"));
                            deviceData.setTemp(rs.getInt("temp"));
                            deviceData.setPh(rs.getInt("ph"));
                            deviceData.setDateTime(rs.getString("eventTime"));
                            list.add(deviceData);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbOpenHelper.closeConnection(connection, ps,rs);
        }
        return list;
    }
}
